These are the steps for seting up the student project preference data in preparation for creating teams and assigning projects.

1. Start the Student Preferences spreadsheet by exporting the data from Qualtrics using the numeric data.
2. Copy the data in the exported CSV file, into a Macro-Enabled Excel spreadsheet which has the NameUnknown macro defined. This will be the Student Preferences spreadsheet where you will do most of the data wrangling of the project preferences data.
3. Delete all columns before the Name except the date recorded column. Eliminate rows 1 and 3. The reminaing top row will have information about the question. Edit the
   preference data columns to have just the name of the sponsor and project name so that you can confirm that the columns align in the Project Information spreadsheet in the next step.
4. In the Project Information spreadsheet, copy the project ids from the named region ProjectDropdown. Insert these aligned with the project names in the first row of the preferences spreadsheet. Make sure that the order of the projects are the same. Adjust columns in the preferences data if they are different. You can delete the project ids or use them as the column heading instead of the full sponsor and project names. They will not get copied to the Project Information spreadsheet.
5. Sort the rows alphabetically by student name and then by the recorded date newest to oldest.
6. Delete all of the 4 (No preferences) entries in the preference data. These values just clutter the field. A blank preference cell will be interpreted as no preference.
7. The survey will validate the requirement for the student to identify at least one 1st, 2nd, and 3rd choice, and no more than one Do Not Want. You will need to check that the student ranked at least 6 projects.
   1. Add a column between the last project column and the "Worked for sponsor" column.
   2. Use this added column to countif(the row of preference values,"<=3"). You can set a conditional format to highlight any entry that is less than 6.
   3. Decide how you want to handle submissions that do not meet this criteria. Reasons that you might use to delete the submission:
      • If you alerted a student to the incorrect submission and the student did not fix it.
      • A correctly identified partner has the required number of ranked projects so that the pair will have at least the required number.
8. Eliminate duplicate entries by a student usually based on keeping the last one submitted. Use your judgement if you think that there are other criteria that would be applicable.
9. Move the On My Team (OMT) and Not On My Team (NOMT) columns next to the student name.
10. At some point, the list of student names from the myCourses community, and the names provided in the preferences survey have to get rectified so that the same name, nickname, etc. is used for each student in the Project Information spreadsheet. Create a second sheet in the Student Preferences spreadsheet. Add the student names and emails from myCourses here. Use conditional formating to indicate which names in the survey data (student name, NOMT) do not match a name in the myCourses enrollment. Decide whether to use the student provided name in the survey or the myCourses name. Make edits to get both lists aligned. You do not need to align the names in the OMT column because that column will disappear. You just have to be able to manually tell which student is being paired so that you can move that student's row in a following step.
11. When you have the correct set of student names to use, copy-and-paste the editted names and emails into the named region StudentInfo in the Students of the Project Information sreadsheet. Now check the names in the Strong Students and Weak Students lists. Make sure they use the names that you settled on.
12. Use conditional formatting to identify the names that did not submit a survey by highlighting any student name in the list of all students that is not in the set of student names for survey submittions. Add a row of "no preferences", i.e. blank cells, to the preferences data for each student who did not submit a survey. This can be in the alphabetic location or at the end of the data.
13. Going down the list of students, look at a name in OMT. Check that student's submission. If the students are correctly paired then move the two rows adjacent to each other. If a student names a student who names someone else or noone as OMT, then remove the OMT name that is not correctly paired.
14. For each correctly paired pair of students, merge their data. Use Alt-Enter to create a line break within a cell.
    1. For the names, NOMT, and text questions at the end, put the data for both students into one cell separated by a linebreak. If the second student has information but the first does not, start the cell with a linebreak so you preserve which student made the comment or provided the information.
    2. For the preference data, if either student has a 5 (Do Not Want) for a project use that. Otherwise, use the highest ranking as the ranking for the pair. For example, if one student has a 1 and the other a 2, it is a 1. If one student has a 4 (No Pref) and the other student has a 3, it is a 3.
    3. After you have merged the data for a student pair, delete the row that you no longer need.
15. Delete the OMT column, and the column added to sum the number of ranked projects.
16. Copy the preference data into the Student Preferences worksheet in the Project Information spreadsheet. Set the named regions on this worksheet and the Assignments worksheet.